package gen.tools.reports;

import gen.tools.Widgets;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

public class GenQueryChkInhisReport extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public GenQueryChkInhisReport() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	@SuppressWarnings({ "deprecation", "resource" })
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		final Widgets wid = new Widgets();
		request.setCharacterEncoding(wid.UIcodeSet);
		response.setCharacterEncoding(wid.UIcodeSet);
		response.setContentType("application/octet-stream; charset=iso-8859-1");
		response.setHeader("Content-disposition",
				"attachment; filename=chkInReport.xls");

		String tmpFileName = System.currentTimeMillis() + ".xls";
		String WebDirPath = request.getSession().getServletContext()
				.getRealPath("/");

		final String MIX_FONT_STYLE = "Verdana";
		final int BASE_WIDTH = 1300;

		int cur = 6;	//目前使用到的row
		
		String sql = "";

		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs3 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		FileInputStream fis = null;
		FileOutputStream fos = null;
		OutputStream out = null;
		
		final HSSFWorkbook wb = new HSSFWorkbook();

		// 設定字型1 英文字，粗體，白色
		final HSSFFont font1 = wb.createFont();
		font1.setFontName(MIX_FONT_STYLE);
		font1.setColor(IndexedColors.WHITE.getIndex());
		font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font1.setFontHeightInPoints((short) 14);
		
		// 設定字型2 英文字，粗體，白色
		final HSSFFont font2 = wb.createFont();
		font2.setFontName(MIX_FONT_STYLE);
		font2.setColor(IndexedColors.BLACK.getIndex());
		font2.setFontHeightInPoints((short) 12);

		//設定字型3 ，一般，黑色，大小10
		final HSSFFont font3 = wb.createFont();
		font3.setFontName(MIX_FONT_STYLE);
		font3.setColor(IndexedColors.BLACK.getIndex());
		font3.setFontHeightInPoints((short) 10);
		
		final CellStyle csvs = wb.createCellStyle();
		csvs.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		csvs.setFont(font3);
		
		// 設定cell style的變數 cs1， 置中、字型1
		final CellStyle cs1 = wb.createCellStyle();
		cs1.setAlignment( HSSFCellStyle.ALIGN_CENTER );
		cs1.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs1.setFont(font1);
		cs1.setWrapText(true);
		cs1.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
		cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		
		final CellStyle cs2 = wb.createCellStyle();
		cs2.setAlignment( HSSFCellStyle.ALIGN_RIGHT );
		cs2.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs2.setFont(font2);
		
		final CellStyle cs3 = wb.createCellStyle();
		cs3.setAlignment( HSSFCellStyle.ALIGN_LEFT );
		cs3.setWrapText(true);//自動換行
		cs3.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs3.setFont(font2);
		
		final CellStyle cs4 = wb.createCellStyle();
		cs4.setAlignment( HSSFCellStyle.ALIGN_CENTER );
		cs4.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs4.setFont(font2);
		
		final HSSFSheet fs = wb.createSheet("查詢結果");
		
		fs.createRow(0).createCell(0).setCellValue(new HSSFRichTextString("製表日期："
				+ wid.changeTimeFormat(new Date())));

		final HSSFRow r5 = fs.createRow(5);

		int count =0;
		final HSSFCell cA5 = r5.createCell(count++);
		cA5.setCellValue("#");
		cA5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cA5.getColumnIndex(), BASE_WIDTH );
		
		final HSSFCell cB5 = r5.createCell(count++);
		cB5.setCellValue("繳交記錄ID");
		cB5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		
		
		final HSSFCell cC5 = r5.createCell(count++);
		cC5.setCellValue("子系統");
		cC5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cC5.getColumnIndex());

		final HSSFCell cD5 = r5.createCell(count++);
		cD5.setCellValue("繳交人員");
		cD5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cD5.getColumnIndex());
		

		final HSSFCell cE5 = r5.createCell(count++);
		cE5.setCellValue("繳交日期");
		cE5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cE5.getColumnIndex());

		final HSSFCell cF5 = r5.createCell(count++);
		cF5.setCellValue("現場版");
		cF5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cF5.getColumnIndex());
		
		final HSSFCell cG5 = r5.createCell(count++);
		cG5.setCellValue("SVN CM\n原始碼版次");
		cG5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cG5.getColumnIndex());
		
		final HSSFCell cH5 = r5.createCell(count++);
		cH5.setCellValue("SVN AP\nMR版次");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cH5.getColumnIndex());
		r5.setHeight((short)700);
		
		final HSSFCell cI5 = r5.createCell(count++);
		cI5.setCellValue("SVN AP\nOL版次");
		cI5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cI5.getColumnIndex());
		
		final HSSFCell cJ5 = r5.createCell(count++);
		cJ5.setCellValue("#");
		cJ5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cJ5.getColumnIndex(), BASE_WIDTH );
		
		final HSSFCell cK5 = r5.createCell(count++);
		cK5.setCellValue("繳交模組");
		cK5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cK5.getColumnIndex());
		
		final HSSFCell cL5 = r5.createCell(count++);
		cL5.setCellValue("產生部署檔");
		cL5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cL5.getColumnIndex());
		
		final HSSFCell cM5 = r5.createCell(count++);
		cM5.setCellValue("部署檔版次");
		cM5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cM5.getColumnIndex());
		
		final HSSFCell cN5 = r5.createCell(count++);
		cN5.setCellValue("流程進度");
		cN5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cN5.getColumnIndex());
		
		final HSSFCell cO5 = r5.createCell(count++);
		cO5.setCellValue("#");
		cO5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cO5.getColumnIndex(), BASE_WIDTH );
		
		final HSSFCell cP5 = r5.createCell(count++);
		cP5.setCellValue("MR否");
		cP5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cP5.getColumnIndex());
		
		final HSSFCell cQ5 = r5.createCell(count++);
		cQ5.setCellValue("繳交程式");
		cQ5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cQ5.getColumnIndex());
		
		final HSSFCell cR5 = r5.createCell(count++);
		cR5.setCellValue("版次");
		cR5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cR5.getColumnIndex());
		
		fs.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

		/******	以上是Table的表頭，以下是表身	******/
	try{
		conn = wid.getConn();
		
		sql = "SELECT t1.chkInMainId,t1.isDailyBuild,t1.cmFileVer,t1.svnMRver,t1.svnOLver,t1.cDT,t2.subsysId,t2.subsysDesc,t3.empId,t3.empName FROM chkinmain t1,subsyses t2,emps t3 WHERE t1.subsysId=t2.subsysId AND t1.creatorId=t3.empId ORDER BY t1.cDT DESC";
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		
		int idx=0;
		while( rs.next() ) {
			final int chkInId = rs.getInt("chkInMainId");
			final String subsysId = rs.getString("subsysId");
			final String subsysDesc = rs.getString("subsysDesc");
			final String empName = rs.getString("empName"); 
			final String actType = "f".equalsIgnoreCase( rs.getString("isDailyBuild") )?"是":"否";
			final String actTypeEng = "f".equalsIgnoreCase( rs.getString("isDailyBuild") )?"OL":"MR";
			int ti=0;	//為了指定 Excel中，column的座標(不是row, 是column)
			
			fs.createRow(cur).createCell(ti++).setCellValue( (1+idx++) );												//流水號
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( chkInId );						//繳交記錄ID
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( subsysDesc+"("+subsysId+")" );	//子系統
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( empName );						//繳交人員
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( changeTimeFormatUI4File( rs.getTimestamp("cDT") ) );		//繳交日期
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.autoSizeColumn(ti-1);
			fs.getRow(cur).createCell(ti++).setCellValue( actType );						//現場版
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( rs.getInt("cmFileVer") );			//SVN CM 原始碼的版次
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( rs.getInt("svnMRver") );			//SVN AP MR 原始碼的版次
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			fs.getRow(cur).createCell(ti++).setCellValue( rs.getInt("svnOLver") );			//SVN AP OL 原始碼的版次
			fs.getRow(cur).getCell(ti-1).setCellStyle(csvs);
			sql= "SELECT modId FROM chkinmod WHERE chkInMainId=?";
			ps2 = conn.prepareStatement(sql);
			ps2.setInt(1, chkInId );
			rs2 = ps2.executeQuery();
			final ArrayList<String> mods = new ArrayList<String>();
			while ( rs2.next() ) {
				mods.add( rs2.getString("modId") );
			}
			int rowBase1 = cur;
			int InchkIn = cur;
			
			sql = "SELECT t2.chkInStatusDesc,t2.chkInStatusId FROM chkinmain t1, chkinstatus t2 WHERE t1.chkInStatusId = t2.chkInStatusId AND t1.chkInMainId=?";
			ps3 = conn.prepareStatement(sql);
			ps3.setInt(1, chkInId );
			rs3 = ps3.executeQuery();
			rs3.next();
			final int chkInStatus = rs3.getInt("chkInStatusId");
			String statusDesc = rs3.getString("chkInStatusDesc");
			
			for( int i=0; i< mods.size(); i++ ) {
				int ti2 = ti;
				final String modId = mods.get(i);
				final String jobName = actTypeEng+"_"+subsysId+"_"+modId;
				String rootJobName = "";
				String depFile = "";		//部署檔名
				int depFileVer = 0;			//部署檔版次
				
				if ( chkInStatus == 5 ) {	//表示繳交即出現問題
					sql = "SELECT rootJobName FROM bemain WHERE chkInMainId=? AND modId=?";
					ps2 = conn.prepareStatement(sql);
					ps2.setInt(1, chkInId );
					ps2.setString(2, jobName);
					rs2 = ps2.executeQuery();
					rs2.next();
					rootJobName= rs2.getString("rootJobName");
					
					sql = "SELECT t1.depFileName,t1.deployFileVer,t1.beStatusId,t2.beStatusDesc FROM bemain t1, bestatus t2 WHERE t1.beStatusId=t2.beStatusId AND t1.chkInMainId=? AND t1.beStatusId<>1 AND t1.rootJobName=?";
					ps2 = conn.prepareStatement( sql );
					ps2.setInt(1, chkInId );
					ps2.setString(2, rootJobName);
					rs2 = ps2.executeQuery();
					rs2.next();
					depFile = rs2.getString("depFileName");		//部署檔名
					depFileVer = rs2.getInt("deployFileVer");			//部署檔版次
					statusDesc = rs2.getString("beStatusDesc");	//進度
				}
				
				if( i==0 ) {
					fs.getRow(rowBase1).createCell(ti2++).setCellValue( (i+1) );	//#
					fs.getRow(rowBase1).getCell(ti2-1).setCellStyle(csvs);
				} else {
					rowBase1++;
					fs.createRow(rowBase1).createCell(ti2++).setCellValue( (i+1) );	//#
					fs.getRow(rowBase1).getCell(ti2-1).setCellStyle(csvs);
				}
				
				fs.getRow(rowBase1).createCell(ti2++).setCellValue( modId );	//模組ID
				fs.getRow(rowBase1).getCell(ti2-1).setCellStyle(csvs);
				fs.autoSizeColumn(ti2-1);
				fs.getRow(rowBase1).createCell(ti2++).setCellValue( depFile );		//產生部署檔
				fs.getRow(rowBase1).getCell(ti2-1).setCellStyle(csvs);
				fs.autoSizeColumn(ti2-1);
				fs.getRow(rowBase1).createCell(ti2++).setCellValue( depFileVer );		//產生部署檔版次
				fs.getRow(rowBase1).getCell(ti2-1).setCellStyle(csvs);
				fs.getRow(rowBase1).createCell(ti2++).setCellValue( statusDesc );		//產生流程進度
				fs.getRow(rowBase1).getCell(ti2-1).setCellStyle(csvs);
				fs.autoSizeColumn(ti2-1);
				sql = "SELECT chkInDetailId,isMR,fileName,relativePath,fileVer FROM chkinmoddetail WHERE chkInMainId=? AND modId=? ORDER BY chkIndetailId";
				ps2 = conn.prepareStatement(sql);
				ps2.setInt(1, chkInId );
				ps2.setString(2, modId);
				rs2 = ps2.executeQuery();
				int a =0;
				int inRow = rowBase1;
				while ( rs2.next() ) {
					int ti3 = ti2;
					final String fromMRSVN = "t".equalsIgnoreCase( rs2.getString("isMR") )?"是":"否";
					final String fileLocation = rs2.getString("relativePath")+rs2.getString("fileName");
					final int fileVer = rs2.getInt("fileVer");

					if( a==0 ) {
						fs.getRow(inRow).createCell(ti3++).setCellValue( a+1 );	//繳交檔案的idx
					} else {
						fs.createRow(++inRow).createCell(ti3++).setCellValue( a+1 );	//繳交檔案的idx
					}
					
					fs.getRow(inRow).createCell(ti3++).setCellValue( fromMRSVN );	//是否來自MR
					fs.getRow(inRow).createCell(ti3++).setCellValue( fileLocation );	//所提交的檔案
					fs.autoSizeColumn(ti3-1);
					fs.getRow(inRow).createCell(ti3).setCellValue( fileVer ); //所提交檔案的版次
					cur++;
					a++;
				}
				//合併儲存格
				
				
				for (int m=9 ; m<14;m++) {
					fs.addMergedRegion(new CellRangeAddress(rowBase1, inRow, m, m));
				}
				rowBase1+=(a-1);
			}
			//合併儲存格
			for (int m=0 ; m<9;m++) {
				fs.addMergedRegion(new CellRangeAddress(InchkIn, cur-1, m, m));
			}
			fs.getRow(InchkIn).setRowStyle(csvs);
		}
			
			
			/* 以上Excel內容製作完成，以下開始產生檔案 */
			
			fos = new FileOutputStream(new File(WebDirPath + tmpFileName));
			wb.write(fos);
			fos.close();
			out = response.getOutputStream();
			fis = new FileInputStream(WebDirPath + tmpFileName);
			byte[] buffer = new byte[4096];
			int length;
			while ((length = fis.read(buffer)) > 0) {
				out.write(buffer, 0, length);
			}
			fis.close();
			out.flush();
			
		} catch (Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( fis !=null ) {fis.close();}
				if ( out !=null ) {out.close();}
				if ( rs !=null ) {rs.close();}
				if ( rs2 !=null ) {rs2.close();}
				if ( rs3 !=null ) {rs3.close();}
				if ( ps3 !=null ) {ps3.close();}
				if ( ps2 !=null ) {ps2.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}
			} catch (SQLException e) {e.printStackTrace();}
		}
	}

	private String changeTimeFormatUI4File(Date dt) {
		return new java.text.SimpleDateFormat("yyyy-MM-dd").format(dt)+" "+new java.text.SimpleDateFormat("HH:mm:ss").format(dt);
	}
}
